#!/usr/bin/env python
# -*- coding: UTF-8 -*-
'''
@Project ：zhcx-service-test 
@File    ：query_sql_method.py
@Author  ：关依林
@Date    ：2023/9/9 21:41 
'''
import csv
import os
import urllib.request
import zipfile
import tempfile
from testcases.ProductGrading_Strategy.AmazonListing.service.listing_grading_public_way import *
from testcases.ProductGrading_Strategy.public_dispatch_way import *

# Mongo连接策略、模板与sku关联表
def connectMongoJava(collName):
    """

    @param collName:
    @return:
    """
    uri = "mongodb://reader:MubmnYb1caAmZjOJ#@116.205.136.83:8635,139.9.210.226:8635/test?authSource=admin"
    dbName = "zhcxkj_java_center"
    cursor = MongoDBLink(uri, dbName, collName)
    return cursor



def getSliceOrderSourceSkuData(id, cursor, data):

    sql = f"SELECT msku FROM t_amazon_listing_{id} WHERE id IN %s"
    res = cursor.execute_sql_many1(sql,(data,))
    return res

def getSliceOrderSourceSkuListing(id, cursor, data):

    sql = f"SELECT msku,asin FROM t_amazon_listing_{id} WHERE id IN %s"
    res = cursor.execute_sql_many1(sql,(data,))
    return res

def getOrderOtherListingSkuData(cursor, id, batchData):
    sql = f"SELECT source_id, sku, msku, country FROM " \
          f"t_amazon_listing_{id} WHERE id IN {batchData}"
    res = cursor.execute_sql_many(sql)
    return res

def getSliceMSkuData(id, cursor, data):

    sql = f"SELECT msku FROM t_amazon_listing_{id} WHERE customer_id = 1 AND is_delete = 0 AND msku IN {data}"
    res = cursor.execute_sql_many(sql)
    orderSourceSkuRes = tuple([i["msku"] for i in res])
    return orderSourceSkuRes

def getFbaListingData(orderSourceId, cursor, sliceOrderSourceSkuData):

    sql = f"SELECT t1.source_id, t1.msku, t1.receiving_time " \
          f"FROM t_amz_fba_shipment_item t1 " \
          f"JOIN (SELECT msku, MAX(id) AS max_id " \
          f"FROM t_amz_fba_shipment_item " \
          f"WHERE source_id = {orderSourceId} AND msku IN {sliceOrderSourceSkuData} AND is_deleted = 0 " \
          f"GROUP BY msku) t2 ON t1.msku = t2.msku AND t1.id = t2.max_id " \
          f"WHERE t1.source_id = {orderSourceId} AND t1.msku IN {sliceOrderSourceSkuData};"
    res = cursor.execute_sql_many(sql)
    return res

def getWarehouseListingData(cursor, orderSourceSkuData, queryFiled):

    filed = ','.join(queryFiled)
    sql = f"SELECT t1.id,t1.source_id,t1.msku,{filed} " \
          f"FROM t_amz_fba_virtual_inventory t1 JOIN t_amz_fba_replenishment_suggestions t2 " \
          f"ON t1.id = t2.id AND t1.customer_id = t2.customer_id " \
          f"WHERE t1.msku IN {orderSourceSkuData}"
    res = cursor.execute_sql_many(sql)
    return res

def getWarehouseFbaListingData(cursor, sourceId, orderSourceSkuData, queryFiled, templateRule=None):

    filed = ','.join(queryFiled)
    sql = f"SELECT t1.id,t1.source_id,t1.msku,{filed} " \
          f"FROM t_amz_fba_virtual_inventory t1 JOIN t_amz_fba_replenishment_suggestions t2 " \
          f"ON t1.id = t2.id AND t1.customer_id = t2.customer_id "  \
          f"WHERE t1.msku IN {orderSourceSkuData} AND t2.is_deleted = 0 " \
          f"AND (t1.msku, t1.id) IN (SELECT msku, MAX(id) FROM t_amz_fba_virtual_inventory WHERE msku IN {orderSourceSkuData} AND source_id = {sourceId} AND is_deleted = 0 " \
          f"GROUP BY msku )"
    res = cursor.execute_sql_many(sql)
    # sliceData = filterFbaListingCondition(res, templateRule, queryFiled)
    return res

# 分表获取Listing数据
def getSubmeterListingData(cursor, id, batchData, queryFiled, isFirst=True):

    filed = ','.join(queryFiled)
    if isFirst:
        sql = f"SELECT source_id, msku, {filed} FROM " \
              f"t_amazon_listing_{id} WHERE id in {batchData}"
    else:
        sql = f"SELECT source_id, msku, {filed} FROM " \
              f"t_amazon_listing_{id} WHERE msku in {batchData}"
    res = cursor.execute_sql_many(sql)
    return res

def getFbaListingInventoryData(orderSourceId, cursor, batchData, queryFiled):

    filed = ','.join(queryFiled)
    sql = f"SELECT t.id, t.source_id,t.msku, t.{filed} " \
          f"FROM t_amz_fba_virtual_inventory t " \
          f"INNER JOIN ( SELECT source_id, msku, max(id) AS max_id " \
          f"FROM t_amz_fba_virtual_inventory " \
          f"WHERE source_id = {orderSourceId} AND msku IN {batchData} AND is_deleted = 0 GROUP BY source_id, msku ) t1 " \
          f"ON t.source_id = t1.source_id AND t.msku = t1.msku AND t.id = t1.max_id"
    res = cursor.execute_sql_many(sql)
    return res

def getSliceData(id, cursor, filedType, data):
    filed = ','.join(filedType)
    sql = f"SELECT source_id, msku, asin, {filed} FROM " \
          f"t_amazon_listing_{id} WHERE id IN %s"
    res = cursor.execute_sql_many1(sql,(data,))
    return res
def getAsinData(id, cursor, data):
    data = tuple([i["msku"] for i in data])
    sql = f"SELECT source_id, msku, asin, sku FROM " \
          f"t_amazon_listing_{id} WHERE msku IN {data}"
    res = cursor.execute_sql_many(sql)
    return res

def getAggregateData(orderSourceId, cursorProduct, sliceData, filedType):
    sliceDataTemp = tuple([i["msku"] for i in sliceData])
    filed = ','.join(filedType)
    sql = f"SELECT source_id, msku, {filed} FROM " \
          f"t_amazon_sales_aggregate WHERE customer_id = 1 AND source_id ={orderSourceId} AND msku IN {sliceDataTemp}"
    res = cursorProduct.execute_sql_many(sql)
    if res:
        temp_list = list(filter(lambda x: x not in str(res), sliceDataTemp))
    else:
        temp_list = sliceDataTemp
        res = []
    for i in temp_list:
        res.append({"msku":i,filed:0,"source_id":orderSourceId})
    return res

def getSliceOrderData(id, cursor, data, country, mark=True):
    if mark:
        filed = "asin_first_order_time"
        sql = f"SELECT t1.source_id, t1.sku, t1.msku, t1.first_order_time, t2.{filed} " \
              f"FROM t_amazon_listing_{id} t1 LEFT JOIN t_amazon_listing_extra_asin_{country.lower()} t2 " \
              f"ON t1.asin = t2.asin AND t1.customer_id = t2.customer_id " \
              f"WHERE t1.id IN {data}"
    else:
        filed = "asin_first_arrival_time"
        sql = f"SELECT t1.source_id, t1.sku, t1.msku, t1.first_arrival_time, t2.{filed} " \
              f"FROM t_amazon_listing_{id} t1 LEFT JOIN t_amazon_listing_extra_asin_{country.lower()} t2 " \
              f"ON t1.asin = t2.asin AND t1.customer_id = t2.customer_id " \
              f"WHERE t1.id IN {data}"
    res = cursor.execute_sql_many(sql)
    return res

def getSubmeterListingOrderData(cursor, id, orderSourceId, condition):

    filed = ','.join(condition)
    sql = f"SELECT source_id, msku, {filed} FROM " \
          f"t_amazon_listing_{id} WHERE customer_id = 1 AND is_deleted = 0 AND source_id = {orderSourceId}"
    res = cursor.execute_sql_many(sql)
    return res

def getListingOrderOtherData(orderCursor, dateBase, customParam, sliceData, country):
    sliceData = list(filter(lambda x: x["sku"] != '' , sliceData))
    tempSliceData = tuple([i["sku"] for i in sliceData])
    if dateBase == "rb_first_order_country":
        sql = f"SELECT sku, country, " \
              f"DATE_FORMAT(MIN(first_order_time), '%Y-%m-%d')AS first_order_time, " \
              f"DATE_FORMAT(DATE_ADD(MIN(first_order_time), INTERVAL {customParam} DAY),'%Y-%m-%d') AS first_order_time_end " \
              f"FROM {dateBase} WHERE sku IN {tempSliceData} AND customer_id = 1 AND is_delete = 0 AND country = '{country}' " \
              f"GROUP BY sku"
        res = orderCursor.execute_sql_many(sql)
        lis = []
        for i in res:
            templis = list(filter(lambda x: x["sku"] == i["sku"], sliceData))
            for x in templis:
                lis.append({"source_id": x["source_id"], "sku": x["sku"], "msku": x["msku"], "country": country,
                            "first_order_time": i["first_order_time"],
                            "first_order_time_end": i["first_order_time_end"]})
    else:
        sql = f"SELECT sku, " \
              f"DATE_FORMAT(MIN(first_order_time), '%Y-%m-%d')AS first_order_time, " \
              f"DATE_FORMAT(DATE_ADD(MIN(first_order_time), INTERVAL {customParam} DAY),'%Y-%m-%d') AS first_order_time_end " \
              f"FROM {dateBase} WHERE sku IN {tempSliceData} AND customer_id = 1 AND is_delete = 0 " \
              f"GROUP BY sku"
        res = orderCursor.execute_sql_many(sql)
        lis = []
        for i in res:
            templis = list(filter(lambda x: x["sku"] == i["sku"], sliceData))
            for x in templis:
                lis.append({"source_id":x["source_id"],"sku":x["sku"],"msku":x["msku"],"country":country,"first_order_time":i["first_order_time"],"first_order_time_end":i["first_order_time_end"]})
    return lis

def getTemplateOrderData(cursor, orderSourceId, sliceData, dateBase, minimum, maximum):
    dateBase = "first_order_time"
    initSql = "SELECT sku, order_source_id AS source_id, order_source_sku AS msku, sum(order_num) AS sum FROM rb_report_order_source_sku_day WHERE "
    sliceData = list(filter(lambda x: x["first_order_time"] != None, sliceData))
    num = len(sliceData)
    sum = 1
    for item in sliceData:
        orderSourceSku = item["msku"]
        starTime = datetime.datetime.strptime(item[dateBase] + ' 00:00:00', '%Y-%m-%d %H:%M:%S')
        endTime = datetime.datetime.strptime(item[dateBase + "_end"] + ' 23:59:59', '%Y-%m-%d %H:%M:%S')
        if sum < num:
            tempFlied = f"(order_source_sku = '{orderSourceSku}' AND sales_day >= '{starTime}' AND sales_day <= '{endTime}') OR "
        else:
            tempFlied = f"(order_source_sku = '{orderSourceSku}' AND sales_day >= '{starTime}' AND sales_day <= '{endTime}') "
        initSql += tempFlied
        sum += 1
    sql =  f"GROUP BY sku, order_source_id, order_source_sku " \
           f"HAVING sum >= {minimum} AND sum <= {maximum} AND order_source_id = {orderSourceId}"
    sql = initSql + sql
    res = cursor.execute_sql_many(sql)
    return res


def getListingOrderData(cursorProduct, id, batchData, dateBase, customParam, isFirst):

    if isFirst:
        sql = f"SELECT source_id, msku, asin, DATE_FORMAT({dateBase}, '%Y-%m-%d') AS {dateBase}, " \
              f"DATE_FORMAT(DATE_ADD({dateBase}, INTERVAL {customParam} DAY),'%Y-%m-%d') AS {dateBase}_end " \
              f"FROM t_amazon_listing_{id} WHERE id IN {batchData}"
    else:
        batchData = tuple([i["msku"] for i in batchData])
        sql = f"SELECT source_id, msku, asin, DATE_FORMAT({dateBase}, '%Y-%m-%d')AS {dateBase}, " \
              f"DATE_FORMAT(DATE_ADD({dateBase}, INTERVAL {customParam} DAY),'%Y-%m-%d') AS {dateBase}_end " \
              f"FROM t_amazon_listing_{id} WHERE msku IN {batchData}"
    res = cursorProduct.execute_sql_many(sql)
    sliceData = list(filter(lambda x: x[dateBase] != None, res))
    return sliceData

def getListingAsinFirstOrderData(cursorListing, country, batchData, customParam):
    data = tuple([i["asin"] for i in batchData])
    sql =  f"SELECT asin, "  \
           f"DATE_FORMAT(asin_first_order_time, '%Y-%m-%d')AS first_order_time, "  \
           f"DATE_FORMAT(DATE_ADD(asin_first_order_time, INTERVAL {customParam} DAY),'%Y-%m-%d') AS first_order_time_end " \
           f"FROM t_amazon_listing_extra_asin_{country.lower()} " \
           f"WHERE asin IN {data} AND customer_id = 1 AND is_deleted = 0 GROUP BY asin;"
    res = cursorListing.execute_sql_many(sql)
    lis = []
    for i in res:
        tempList = list(filter(lambda x: x["asin"] == i["asin"], batchData))
        for x in tempList:
            lis.append({"asin":x["asin"],"source_id": x["source_id"], "sku": x["sku"], "msku": x["msku"], "country": country,
                        "first_order_time": i["first_order_time"],
                        "first_order_time_end": i["first_order_time_end"]})
    return lis
def getListingFirstOrderData(cursorListing, country, batchData, dateBase, customParam):
    sliceData = list(filter(lambda x: x["sku"] != '', batchData))
    tempSliceData = tuple([i["sku"] for i in sliceData])
    if dateBase == "t_amazon_listing_extra_sys_sku_country":
        sql = f"SELECT sku, " \
              f"DATE_FORMAT(MIN(platform_country_first_order_time), '%Y-%m-%d')AS first_order_time, " \
              f"DATE_FORMAT(DATE_ADD(MIN(platform_country_first_order_time), INTERVAL {customParam} DAY),'%Y-%m-%d') AS first_order_time_end " \
              f"FROM {dateBase} WHERE sku IN {tempSliceData} AND customer_id = 1 AND is_deleted = 0 AND country = '{country}' " \
              f"GROUP BY sku"
        res = cursorListing.execute_sql_many(sql)
        lis = []
        for i in res:
            tempList = list(filter(lambda x: x["sku"] == i["sku"], sliceData))
            for x in tempList:
                lis.append({"source_id": x["source_id"], "sku": x["sku"], "msku": x["msku"], "country": country,
                            "first_order_time": i["first_order_time"],
                            "first_order_time_end": i["first_order_time_end"]})
    else:
        sql = f"SELECT sku, " \
              f"DATE_FORMAT(MIN(platform_first_order_time), '%Y-%m-%d')AS first_order_time, " \
              f"DATE_FORMAT(DATE_ADD(MIN(platform_first_order_time), INTERVAL {customParam} DAY),'%Y-%m-%d') AS first_order_time_end " \
              f"FROM {dateBase} WHERE sku IN {tempSliceData} AND customer_id = 1 AND is_deleted = 0 " \
              f"GROUP BY sku"
        res = cursorListing.execute_sql_many(sql)
        lis = []
        for i in res:
            tempList = list(filter(lambda x: x["sku"] == i["sku"], sliceData))
            for x in tempList:
                lis.append({"source_id": x["source_id"], "sku": x["sku"], "msku": x["msku"], "country": country,
                            "first_order_time": i["first_order_time"],
                            "first_order_time_end": i["first_order_time_end"]})
    return lis


def getHolidayListingTemp(cursor, id, sourceId, batchData):

    batchDataSku = tuple([i["msku"] for i in batchData])
    sql = f"SELECT source_id, msku, tag_id " \
          f"FROM t_amazon_listing_tag_{id}  " \
          f"WHERE source_id = {sourceId} and msku in {batchDataSku}"
    res = cursor.execute_sql_many(sql)
    return res

def getStarListingData(cursor, id, batchData, sourceId):

    batchData = tuple([i["asin"] for i in batchData])
    sql = f"SELECT source_id, asin, rating_star " \
          f"FROM t_amazon_listing_extra_{id} " \
          f"WHERE source_id = {sourceId} and asin in {batchData}"
    res = cursor.execute_sql_many(sql)
    return res

def getHolidayStarListingData(cursor, id, sourceId, batchData, base, ids, country):

    filed = ",".join(ids)
    if country:
        sql = f"SELECT {filed} " \
              f"FROM {base}{country} " \
              f"WHERE {ids[0]} in %s"
    else:
        sql = f"SELECT source_id, {filed} " \
              f"FROM {base}{id}{country} " \
              f"WHERE source_id = {sourceId} and {ids[0]} in %s"
    res = cursor.execute_sql_many1(sql, (batchData,))
    return res

# 获取指定SKU
def getSpecifySkuData(cursorStrategy, strategyID):

    sql = f"SELECT appoint_type,appoints  FROM t_strategy_appoint_info WHERE is_deleted = 0 AND strategy_id = {strategyID}"
    condition = cursorStrategy.execute_sql_many(sql)
    return condition

# 获取策略条件
def getStrategyCondition(cursorStrategy, strategyID):
    sql = f"SELECT platform_no_sale,product_risk_level FROM t_strategy_target WHERE is_deleted = 0 AND strategy_id = {strategyID}"
    condition = cursorStrategy.execute_sql_many(sql)
    return condition

# 获取策略模板
def getStrategyTemplate(cursorStrategy, strategyID):

    sql = f"SELECT template_ids FROM t_strategy_rule WHERE is_deleted = 0 AND strategy_id = {strategyID}"
    condition = cursorStrategy.execute_sql_many(sql)
    return condition

# 获取策略指定站点/渠道 or 平台禁售/产品风险等级
def getSpecifyCountryData(cursorStrategy, strategyID):
    sql = f"SELECT target_type,target_value FROM t_strategy_target WHERE is_deleted = 0 AND strategy_id = {strategyID}"
    condition = cursorStrategy.execute_sql_many(sql)
    return condition

def getSpecifyFbaData(cursorStrategy, strategyID):
    sql = f"SELECT fbm_template_id FROM t_strategy_target WHERE is_deleted = 0 AND strategy_id = {strategyID}"
    condition = cursorStrategy.execute_sql_many(sql)
    return condition

# 获取产品分级策略Sku数据
def getStrategyResult(comparison, categoryId):
    mongoCursor = connectMongo("t_sku_strategy_relation")
    sql = comparison["sql"]
    sql["strategy_relation.strategy_id"] = categoryId
    field = comparison["field"]
    strategyResult = mongoCursor.select2(sql, field)
    return strategyResult

# 获取策略指定产品分级策略
def getProductStrategy(cursorStrategy, strategyID):

    sql = f"SELECT relation_strategy_ids FROM t_strategy WHERE is_deleted = 0 AND id = {strategyID}"
    condition = cursorStrategy.execute_sql_many(sql)
    return condition

# 判断SKU平台禁售
def productNoSaleJudge(cursorProduct, batchData):

    sql = f"SELECT sku,country_shorthand FROM rb_product_no_sale " \
          f"WHERE customer_id = 1 AND platform_id = 1  AND sku IN {batchData}"
    condition = cursorProduct.execute_sql_many(sql)
    return condition

# 过滤风险等级及禁售平台
def getProductRiskLevelJudge(cursorProduct, batchData):

    # 查询sku风险等级
    sql = f"SELECT sku FROM rb_product_admin WHERE customer_id = 1 AND risk_level_type=1 AND sku in {batchData};"
    riskSku = cursorProduct.execute_sql_many(sql)
    if riskSku:
        if len(riskSku) == 1:
            sku = riskSku[0]['sku']
            batchData = f"('{sku}')"
        else:
            batchData = tuple([i["sku"] for i in riskSku])
        # 通过sku过滤是否对应平台国家
        sql = f"SELECT sku,country_shorthand FROM rb_product_risk_level_platform WHERE customer_id = 1 AND platform_id = 1 AND sku in {batchData};"
        riskSku = cursorProduct.execute_sql_many(sql)
    return riskSku

# 获取Listing策略下所有绑定数据
def getStrategyListingResult(categoryId):

    mongoCursor = connectMongo(f"t_strategy_template_amazon_listing_{categoryId}")
    sql = {}
    field = {"_id":0,"source_id": 1,"sku":1,"msku": 1,"asin": 1}
    strategyResult = mongoCursor.selectTuple2(sql, field)
    return strategyResult

def getTemplateFbaResult(categoryId, batchData):

    mongoCursor = connectMongo(f"t_strategy_template_amazon_listing_{categoryId}")
    sql = {"listing_id": {"$in": batchData}}
    field = {"_id":0,"asin": 1,"source_name":1}
    strategyResult = mongoCursor.selectTuple2(sql, field)
    return strategyResult

def getTemplateListingDataTrue(categoryId, orderSourceId, isFbaFilter):

    mongoCursor = connectMongo(f"t_strategy_template_amazon_listing_{categoryId}")
    sql = {"source_id": orderSourceId}
    field = {"_id": 0, "listing_id": 1, "sku": 1}
    if isFbaFilter:
        field = {"_id": 0, "listing_id": 1, "asin": 1, "sku": 1}
    strategyResult = mongoCursor.select2(sql, field)
    # strategyResult = [i[fieldName] for i in strategyResult]
    return strategyResult

def getStrategyListingData(categoryId, orderSourceId, skuSet):

    mongoCursor = connectMongo(f"t_strategy_amazon_listing_{categoryId}")
    sql = {"listing_id": {"$in": skuSet}}
    field = {"_id":0, "listing_id": 1}
    strategyResult = mongoCursor.select2(sql, field)
    strategyResult = [i["listing_id"] for i in strategyResult]
    return strategyResult

def getTemplateListingDataFalse(categoryId, data):

    mongoCursor = connectMongo(f"t_strategy_template_amazon_listing_{categoryId}")
    sql = {"listing_id": {"$in": data}}
    field = {"_id":0,"listing_id": 1,"asin": 1, "sku": 1}
    strategyResult = mongoCursor.select2(sql, field)
    return strategyResult

def getStrategyListingIntersectionResult(sourceId,templateId):
    tempBase = ""
    num = 1
    for id in templateId:
        if num < len(templateId):
            base = "{'$lookup': {'from': " + f"'t_strategy_template_amazon_listing_{id}'" + ",'localField': 'listing_id','foreignField': 'listing_id'," + f"'as': 'comm_template{num}'" + "}},"
            num += 1
            tempBase += base
        else:
            base = "{'$lookup': {'from': " + f"'t_strategy_template_amazon_listing_{id}'" + ",'localField': 'listing_id','foreignField': 'listing_id'," + f"'as': 'comm_template{num}'" + "}}"
            tempBase +=base
    pipeline = ("{'$match': {'source_id': " + f"{sourceId}" + "}}," + f"{tempBase},"
                "{'$project': {'source_id': 1,'source_name': 1,'asin': 1,'sku': 1,'msku': 1,'source_id': 1,'listing_id': 1}},{'$limit': 3000}")
    mongoCursor = connectMongo(f"t_strategy_template_amazon_listing_{templateId}")
    strategyResult = mongoCursor.aggregate([pipeline])
    return strategyResult

# 获取该渠道下所有SKU数据
def getTemplateListingSku(cursorListing, id, listingId):

    sql = f"SELECT sku FROM " \
          f"t_amazon_listing_{id} WHERE id in {listingId}"
    res = cursorListing.execute_sql_many(sql)
    return res

# 获取该店铺所属国家
def getSourceCountry(cursorData, orderSourceId):
    sql = f"SELECT order_source_country FROM " \
          f"rb_order_source WHERE id = {orderSourceId}"
    res = cursorData.execute_sql_many(sql)
    return res[0]["order_source_country"]

def getTemplateCount(cursorStrategy, templateId):
    sql = "SELECT id, bind_count FROM " \
          "t_strategy_template WHERE id IN %s"
    res = cursorStrategy.execute_sql_many1(sql, (templateId,))
    return res

def getSameAsinCountryListing(listingId, tempListingId, sourceCountry):
    mongoCursor = connectMongo(f"t_strategy_template_amazon_listing_{tempListingId}")
    lis = []
    for sku in range(0, len(listingId), 1000):
        batchData = listingId[sku: sku + 1000]
        asinSet = [i["asin"] for i in batchData]
        sql = {"asin": {"$in": asinSet},"country":sourceCountry}
        field = {"_id": 0, "asin": 1}
        strategyResult = mongoCursor.select2(sql, field)
        strategyResult = [i["asin"] for i in strategyResult]
        strategyResult =  list(filter(lambda x: x["asin"] not in strategyResult, batchData))
        lis += strategyResult
    # strategyResult = [str(i["listing_id"]) for i in lis]
    return lis

def getStrategyData(cursorStrategy, strategyId, orderSourceId):
    nowTime = datetime.datetime.now().strftime('%Y-%m-%d')
    # nowTime = '2023-12-14'
    sql = (f"SELECT document_url FROM t_strategy_result_log "
           f"WHERE strategy_id = {strategyId} AND source_id = {orderSourceId} AND document_date = '{nowTime}' "
           f"ORDER BY id LIMIT 1;")
    url = cursorStrategy.execute_sql_many(sql)[0].get("document_url", None)
    data = []
    if url:
        path = 'D:\\zhcx-service-test\\common'
        file_name = url.split("/" + nowTime + "/")[-1].split(".")[0] + '.csv'
        file_path = os.path.join(path, file_name)
        # 下载csv文件
        urllib.request.urlretrieve(url, file_path)
        with open(file_path, newline='') as csvfile:
            reader = csv.reader(csvfile, delimiter=',', quotechar='"')
            next(reader)  # 跳过标题行
            for row in reader:
                data.append(row[3])
    return data


# 将接收到的数据转换Sku
def getMSkuConvertSku(id, orderSourceId, cursorListing, mskuSet, IsMSku):
    if IsMSku:
        sql = f"SELECT id, sku FROM t_amazon_listing_{id} WHERE source_id = {orderSourceId} AND msku IN {mskuSet}"
        res = cursorListing.execute_sql_many(sql)
    else:
        sql = f"SELECT id, sku FROM t_amazon_listing_{id} WHERE source_id = {orderSourceId} AND asin IN {mskuSet}"
        res = cursorListing.execute_sql_many(sql)
    return res


